Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

' Para gerar Excel
Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Office.Interop

Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

' Para gerar Excel
Imports System.Reflection
Imports System.Runtime.InteropServices

Partial Class ImpRelOrdemCompra
    Inherits System.Web.UI.Page

    Private Function CarregaGVWDetail() As Boolean

        Dim strWhere As String = ""
        Dim strDataDe As String = ""
        Dim strDataAte As String = ""

        CarregaGVWDetail = False

        strDataDe = Format(CDate(Session("strDataDe").ToString), "yyyy-MM-dd HH:mm:ss")
        strDataAte = Format(CDate(Session("strDataAte").ToString), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaHolding, CodigoDaCia, CodigoDoEstabelecimento," & _
                     " OrdemCompra, SeqOrdemCompra, DataOrdemCompra, ValorTotalOrdemCompra," & _
                     " CodFornecedor, RazaoSocialFornecedor, SiglaDaMoeda, DscMeioTransporte," & _
                     " DscCondicaoPagamento, CodImportador, RazaoSocial," & _
                     " LinhaOrdemCompra, CodItem, DscItem, CodNaturezaOperacao," & _
                     " ROUND(QtdeOrdemCompra,3) AS QtdeOrdemCompra, SimboloUnidade, ROUND(ValorUnitarioItem,4) AS ValorUnitarioItem," & _
                     " ValorTotalItem, SiglaDaMoedaD, CodIncoterm," & _
                     " DscMeioTransporteD, DscCondicaoPagamentoD, DataPrevistaChegada" & _
                     " FROM Rep_OrdemCompra" & _
                     " WHERE CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "'" & _
                     " AND CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento").ToString & "'" & _
                     " AND DataOrdemCompra BETWEEN '" & strDataDe & "' AND '" & strDataAte & "'" & _
                     " ORDER BY OrdemCompra, SeqOrdemCompra, LinhaOrdemCompra;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwDetail.DataSource = mydatatable
                gvwDetail.DataBind()

                CarregaGVWDetail = True

                theSqlDataReader.Close()

            Catch ex As Exception
                Session.Add("MsgException", ex.Message)
            Finally
                'theSqlDataReader.Close()
            End Try

        End Using

    End Function

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        If Not Page.IsPostBack Then

            Dim ConnectionTest As String
            ConnectionTest = "A"

            'btnImprimir.Enabled = False

            If (CarregaGVWDetail() = True) Then

                If (gvwDetail.PageCount = 0) Then
                    Response.Redirect("RelOrdemCompra.aspx")
                Else
                    If (Not (ExportaExcel01() = True)) Then

                    End If
                    'Response.Redirect("RelPesoDoEmpregadoNoCentroDeCusto.aspx")
                End If
            End If
        End If

    End Sub

    Private Function ExportaExcel01() As Boolean

        ExportaExcel01 = False

        Try
            Dim frm As HtmlForm = New HtmlForm

            'gvwData.AllowPaging = False
            'gvwData.DataBind()

            Response.Clear()
            Response.AddHeader("content-disposition", "attachment; filename=RepOrdemCompra.xls")
            Response.Charset = ""
            'Response.ContentType = "application/vnd.xls"
            Response.ContentType = "application/vnd.ms-excel"

            ' If you want the option to open the Excel file without saving than comment out the line below
            Response.Cache.SetCacheability(HttpCacheability.NoCache)

            'Response.ContentType = "application/vnd.xls"

            'gvwData.Page.EnableViewState = False
            EnableViewState = False
            'Controls.Add(frm)
            'frm.Controls.Add(gvwData)

            'Dim strBuilder As New StringBuilder("")
            Dim stringWrite As New StringWriter()
            'System.IO.StringWriter(stringWrite = New System.IO.StringWriter())

            Dim htmlWrite As New HtmlTextWriter(stringWrite)
            'System.Web.UI.HtmlTextWriter(htmlWrite = New HtmlTextWriter(stringWrite))

            'frm.RenderControl(htmlWrite)
            gvwDetail.RenderControl(htmlWrite)

            Response.Write(stringWrite.ToString())

            Response.End()

            ExportaExcel01 = True
            'gvwData.AllowPaging = True
            'gvwData.DataBind()

        Catch ex As Exception
            'lblMsg.Text = ex.Message
        End Try

    End Function

End Class
